﻿using Dapper;

using HBase;
using hCommand;
using Net.http;
using System;
using System.Collections.Generic;
using System.Linq;
using static Dapper.SqlMapper;

namespace Persistence {
    public class AbsStatServer : AbsExtendsServer, IStatServer {
       

        public void statDoi() { 
           List<WhDoiDet> doiDets= opConn().Query<WhDoiDet>("select * from wh_doi_det where cv>0").ToList();
           foreach (WhDoiDet det in doiDets) {
                String sql = "update wh_doi_det set finsh_count= (select IFNULL( Sum(count_DB),0),cv=0 from core_stock_det  where doi_det_id=@id  and status={0} and pId >0),cv=0 where id=@id and cv=@cv";
                int c = opConn().Execute(HUtil.s(sql, StockDetailStatus.库存), det);
                if (c > 0) {
                    String countSql = "SELECT count(*) FROM  wh_doi_det n    where m.pid=@pid  and n.plan_Count<>n.finsh_Count";
                    int woringDet = opConn().QueryUnique<int>(countSql, det);
                    if (woringDet == 0) {
                        opConn().Execute("update wh_doi set status=9,finsh_time=sgj_get_sys_now where id=(select pid from wh_doi_det where id=@id)", det);
                    }
                }
            }
        }

        public void statDoo() {
            List<WhDooDet> dooDets = opConn().Query<WhDooDet>("select * from wh_doo_det where cv>0").ToList();
            foreach (WhDooDet det in dooDets) {
                String sql = "update wh_doo_det set finsh_count=(select IFNULL( Sum(count_DB),0) from core_stock_det  where doo_det_id=@id and status>70 ),cv=0 where id=@id and cv=@cv";
                int c = opConn().Execute(sql, det);
                if (c > 0) {
                    String countSql = "SELECT count(*) FROM wh_doo_det n   where m.pid=@pid  and n.plan_Count-n.finsh_Count>" + CsdUtil.loss;
                    int woringDet = opConn().QueryUnique<int>(countSql, det);
                    if (woringDet == 0) {
                        opConn().Execute("update wh_doo set status=9,finsh_time=sgj_get_sys_now where id=(select pid from wh_doo_det where id=@id)", det);
                    }
                }
            }
          
        }
        public void statBatch() { 
            List<WhBatch> batchs = opConn().Query<WhBatch>("select * from wh_batch where cv>0").ToList();
            foreach (WhBatch batch in batchs) {

                String countSql = "select count(*) from core_stock_det  where  batch_Id=@id  and status<{0}  and status>{1}" ;
                countSql = HUtil.s(countSql, StockDetailStatus.上限, StockDetailStatus.下线);
                double count = opConn().QueryUnique<double>(countSql, batch);
                opConn().Execute("update core_item set cv=cv+1 where id in @itemId", batch);
                if (count > 0) {
                    String updateSql = "update Wh_Batch set count=(select Sum(count_DB) from core_stock_det  where  batch_Id=@id  and status={0}),cv=0 where id=@id and cv=@cv";
                    opConn().Execute(HUtil.s(updateSql, StockDetailStatus.库存), batch);
                } else {
                    opConn().Execute("delete Wh_Batch  where id=@id", batch);
                }
            }
        }
        public void statItme() {
            List<CoreItem> items = opConn().Query<CoreItem>("select * from core_item where cv>0").ToList();
            foreach (CoreItem item in items) {
                String sql = "update Core_Item set count=(select IFNULL(  sum(count),0) from wh_batch  where  item_id=@id ) ,cv=0 where id=@id and cv=@cv";
                int c = opConn().Execute(sql, item);
            }
        }

        public void statData() {
            BParam p= opConn().GetById<BParam>(999);
            var n = DateTime.Now;
            DateTime b;
            if (p.v < 20200101) { 
                b = new DateTime(n.Year, n.Month, n.Day);
            } else {
                b = new DateTime((int)(p.v/10000),(int)(p.v%10000)/100,(int) (p.v % 100));
            }

            var e = b.AddDays(1);
            var betw = new { b, e };
            String sql = "SELECT sum(s.count_db) in_Count,s.item_Id  FROM core_stock_det s    where s.create_Time>@b  and s.create_Time>@e and status>{0}  GROUP BY s.item_Id";
            List<WhDoStat> inStat = opConn().Query<WhDoStat>(HUtil.s(sql, StockDetailStatus.下线), betw).ToList();
            
            String outSql = "SELECT sum(s.count_db) out_Count,s.item_Id  FROM core_stock_det s  where s.status>{0} and s.status<{1} and s.out_Time>@b and s.out_Time<@e  GROUP BY s.item_Id";
            List<WhDoStat> outStat = opConn().Query<WhDoStat>(HUtil.s(outSql, StockDetailStatus.上限, StockDetailStatus.直接编辑出库), betw).ToList();
            foreach (WhDoStat os in outStat) {
                checkOut(os, inStat);
            }
            for (int i = 0; i < inStat.Count; i++) {
                inStat[i].createTime =b;
            }
            opConn().Execute("delete  from Wh_Do_Stat where create_Time= @b", betw);

            if (inStat.Count > 0) {
                opConn().BatchInsert(inStat);
                String update = "update Wh_Do_Stat s set remain=(select IFNULL(count,0)  from core_item WHERE id=s.item_id) where  create_Time=@b";
                opConn().Execute(update);

            }
            if (e.Ticks<n.Ticks) {
                p.v = e.Year * 10000 + e.Month * 100 + e.Day;
                opConn().Execute("update B_Param set v=@v where id=@id", p);
            }
        
        }



        void checkOut(WhDoStat os, List<WhDoStat> inStat) {
            for (int i = 0; i < inStat.Count; i++) {
                if (inStat[i].itemId == os.itemId) {
                    inStat[i].outCount = os.outCount;
                    return;
                }
            }
            inStat.Add(os);
        }

    }
}
